﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using newmanager;
using Excel = Microsoft.Office.Interop.Excel;

namespace newmanager.PMSClass
{
    class Operate
    {
        string constr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
        public static string permissions = "1";
        public SqlHelper sqlhelper = new SqlHelper();

        public void BindDgv(DataGridView dg, string sql)     // 绑定Dgv
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(constr))
                {
                    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    dg.DataSource = ds.Tables[0];
                    ds.Dispose();
                }
            }
            catch 
            {
                MessageBox.Show("连接数据库有误");
            }
        }

        public void Bindalldgv(DataGridView dg_admin,DataGridView dg_user,DataGridView dg_export,DataGridView dg_activity,DataGridView dg_realse) 
        {
            string sql1 = "select UserName as '用户名',MobilePhone as '手机号',QQ as 'QQ号',RegistrationTime as '注册时间' from bm_User where Permissions=N'1' order by id desc";
            BindDgv(dg_admin, sql1);
            string sql2 = "select UserName as '用户名',MobilePhone as '手机号',QQ as 'QQ号',Suspended as '是否交费',Lasttime as '上次交费时间',Expirationtime as '到期时间',RegistrationTime as '注册时间',Referees as '推荐人' from bm_User where Permissions=N'0' order by id desc";
            BindDgv(dg_user, sql2);
            string sqlexport = "select UserName as '用户名',UserType as '用户类型',RealName as '联系人',Sex as '性别',IdentityID as '身份证号',CompanyName as '公司名称',CompanyAddress as '公司地址',CompanyProfile as '公司简介',CorporateLAN as '公司专线',Province as '所在省',City as '所在市',County as '所在县',MobilePhone as '手机号',QQ as 'QQ号',FixedTelephone as '固定电话',Fax as '传真',Email as '邮箱',Suspended as '是否交费',Lasttime as '上次交费时间',Expirationtime as '到期时间',RegistrationTime as '注册时间',Referees as '推荐人' from bm_User where Permissions=N'0' order by id desc";
            BindDgv(dg_export, sqlexport);
            string sql3 = "select id as '编号',theme as '活动主题',[Content] as '活动内容',Time as '发布时间' from bm_Activity order by id desc";
            BindDgv(dg_activity, sql3);
            string sql4 = "select UserName as '用户名',UserType as '用户类型',RealName as '联系人',Sex as '性别',IdentityID as '身份证号',CompanyName as '公司名称',CompanyAddress as '公司地址',CompanyProfile as '公司简介',CorporateLAN as '公司专线',Province as '所在省',City as '所在市',County as '所在县',MobilePhone as '手机号',QQ as 'QQ号',FixedTelephone as '固定电话',Fax as '传真',Email as '邮箱',Suspended as '是否交费',Lasttime as '上次交费时间',Expirationtime as '到期时间',RegistrationTime as '注册时间',Referees as '推荐人' from bm_User where Permissions=N'0' order by id desc";
            BindDgv(dg_realse, sql4);
        }

        public bool Export(DataGridView dgv, bool isShowExcle,int judge)
        {
            if (dgv.Rows.Count == 0)
                return false;
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcle;
            for (int i = 0; i <= dgv.ColumnCount - 1; i++)
            {
                excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;

            }
            if (judge == 1)
            {
                DateTime today = DateTime.Today;
                DateTime lastday = today.AddMonths(-1);
                DateTime tabletime;
                int count = 2;
                for (int i = 0; i <= dgv.RowCount - 1; i++)
                {

                    tabletime = Convert.ToDateTime(dgv[20, i].Value);
                    if (tabletime < lastday)
                        continue;

                    for (int j = 0; j <= dgv.ColumnCount - 1; j++)
                    {

                        if (dgv[j, i].ValueType == typeof(string))
                        {
                            excel.Cells[i + 2, j + 1] = "" + dgv[j, i].Value.ToString();
                        }
                        else
                        {
                            excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
                        }

                    }
                    count++;
                }
                return true;
            }
            else
            {
                for (int i = 0; i <= dgv.RowCount - 1; i++)
                {
                    for (int j = 0; j <= dgv.ColumnCount - 1; j++)
                    {

                        if (dgv[j, i].ValueType == typeof(string))
                        {
                            excel.Cells[i + 2, j + 1] = "" + dgv[j, i].Value.ToString();
                        }
                        else
                        {
                            excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
                        }
                    }
                }
            }
            return true;
        }

        public void Judgeadd(TextBox tb_username, TextBox tb_password, TextBox tb_passwordag)
        {
            try
            {
                string str = "select count(*) from bm_User where UserName=N'" + tb_username.Text.Trim() + "'";
                int i = sqlhelper.SqlScalar(str); //是否存在用户名
                if (i > 0)
                {
                    MessageBox.Show("用户名已存在");
                    return;
                }
                else
                {
                    if (tb_username.Text.Length > 50 || tb_username.Text == "")  //用户名长度判断
                    {

                        MessageBox.Show("用户名长度有误");
                        tb_username.Focus();
                        return;
                    }
                    if (tb_password.Text.Length > 16 || tb_password.Text.Length < 6) //密码长度判断
                    {
                        MessageBox.Show("密码长度有误");
                        tb_password.Focus();
                        return;
                    }

                    if (tb_passwordag.Text != tb_password.Text)    //密码一致性判断
                    {
                        MessageBox.Show("两次输入的密码不一致");
                        tb_password.Focus();
                        return;
                    }
                    string l_sql = "Insert into bm_User (UserName,PassWord,Permissions,RegistrationTime) values ('" + tb_username.Text + "' ,'" + tb_password.Text + "','1','" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "')";
                    sqlhelper.SqlNonQuery(l_sql);
                    MessageBox.Show("增加管理员成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch
            {
                MessageBox.Show("增加管理员失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        public void Delect(DataGridView dgv) 
        {
            string usr = dgv.SelectedCells[0].Value.ToString();
            if (dgv.SelectedCells.Count == 0)
            {
                MessageBox.Show("请选择要删除的管理员", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            else
            {
                DialogResult dr = MessageBox.Show("您确定要删除用户" + usr + "吗", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Hand);
                if (dr == DialogResult.Yes)
                {
                    string sql = "delete  from bm_User where UserName='" + usr + "'";
                    sqlhelper.SqlNonQuery(sql);
                    string sql2 = "delete  from bm_History where UserName='" + usr + "'";
                    sqlhelper.SqlNonQuery(sql2);
                    MessageBox.Show("管理员删除成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }

        }

        public void Page3(TextBox tb_theme,RichTextBox rtb_information,string str) 
        {
            try
            {
                if (tb_theme.Text == "")
                {
                    MessageBox.Show("主题不能为空！");
                    return;
                }
                if (rtb_information.Text == "")
                {
                    MessageBox.Show("信息内容也不能为空！");
                    return;
                }
                sqlhelper.SqlNonQuery(str);
                //---清空所有信息----------------
                MessageBox.Show("操作成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch
            {
                MessageBox.Show("操作失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

    }
}
